rm(list=ls())
library(tidyverse)
library(readxl)

# Read and structure mun population from 2000 to 2019

read_pop_by_age <- function(y,tab){
 total <- read_excel(tab,
             sheet = paste0(y,"; Total"),
             skip = 4,
             col_names = c("mun_code","mun_name","sex","var_desc","situation","mun_pop_total"),
             col_types = c(rep("text",5),"numeric"),
             na = "...") %>% 
   filter(!is.na(mun_name)) %>% 
   select(-sex,-var_desc,-situation)
 
 zero_four <- read_excel(tab,
                     sheet = paste0(y,"; 0 a 4 anos"),
                     skip = 4,
                     col_names = c("mun_code","mun_name","sex","var_desc","situation","mun_pop_0_4"),
                     col_types = c(rep("text",5),"numeric"),
                     na = "...") %>% 
   filter(!is.na(mun_name)) %>% 
   select(-mun_name,-sex,-var_desc,-situation)
 
 five_nine <- read_excel(tab,
                     sheet = paste0(y,"; 5 a 9 anos"),
                     skip = 4,
                     col_names = c("mun_code","mun_name","sex","var_desc","situation","mun_pop_5_9"),
                     col_types = c(rep("text",5),"numeric"),
                     na = "...") %>% 
   filter(!is.na(mun_name)) %>% 
   select(-mun_name,-sex,-var_desc,-situation)
 
 ten_fourteen <- read_excel(tab,
                     sheet = paste0(y,"; 10 a 14 anos"),
                     skip = 4,
                     col_names = c("mun_code","mun_name","sex","var_desc","situation","mun_pop_10_14"),
                     col_types = c(rep("text",5),"numeric"),
                     na = "...") %>% 
   filter(!is.na(mun_name)) %>% 
   select(-mun_name,-sex,-var_desc,-situation)
 
 fifteen <- read_excel(tab,
                     sheet = paste0(y,"; 15 anos"),
                     skip = 4,
                     col_names = c("mun_code","mun_name","sex","var_desc","situation","mun_pop_15"),
                     col_types = c(rep("text",5),"numeric"),
                     na = "...") %>% 
   filter(!is.na(mun_name)) %>% 
   select(-mun_name,-sex,-var_desc,-situation)
 
 df <- total %>% 
   left_join(zero_four, by = "mun_code") %>% 
   left_join(five_nine, by = "mun_code") %>% 
   left_join(ten_fourteen, by = "mun_code") %>% 
   left_join(fifteen, by = "mun_code") %>% 
   mutate(year = y)
 
 return(df)
 
}  

file <- list.files(here::here("data","raw","IBGE"), pattern = "tab200", full.names = T)

pop_mun_by_age <- map(c(2000,2010), read_pop_by_age, tab = file) %>% 
  bind_rows()

# Save
write_rds(pop_mun_by_age,here::here("data","processed","citycharacteristics","pop_mun_by_age.rds"))
